Wow, this board has gotten really busy lately - maybe 2007 is the year that a lot more people start using SSIS :) Anyway my question is this: If I have an ordered set of data in the data flow and I want to add a column, lets just say "previousID" that basically has the ID value of a column from the row immediately before it - what is the most effecient way of doing that? I've done much more complicated things with running averages, mean, etc by creating an asynchronous script transformation, pushing the data into a datatable in memory and looping through row by row using variables etc to do the calcs... but I just have this feeling that there is a "lighter, faster, easier" way for just getting previous row's value (with some special rows like first row has a null etc) than looping through a datatable row by row. Can you push the buffer into an array (if so anyone have an example script) and use simple "n-1" logic? (ie using the array index)

Use a script component. Should be easy enough to do. Create a variable and assign it the current row's value. Then, in the next row, you read in that variable (derived column) to be used in your data flow. Repeat.

Yep, I'd do it the same way as Phil. Another option would be to add two new columns called 'ID' and 'IDplusOne'. Assign 'ID' an integer value and then add one to that value and put it into 'IDplusOne'. Use a Merge Join to create two identical data paths and join them using a MERGE JOIN component where 'ID' = 'IDplusOne' -Jamie

Sounds simple enough... I guess I was overthinking it... let me give it a try (I simplified the actual problem for sake of a clear post but there is no reason Phil's approach shouldn't work for what I am actually doing).

Works like a charm... since I always like example scripts here's mine for anyone else that may come across this post with a similar question. This code provides a "sign" basically saying if a Price was higher or lower (or same) as the record immediately before it... useful for trending (and works pretty well as an example for this). It leaves the first row passed as NULL. Public Class ScriptMain Inherits UserComponent Dim dPrice As Decimal = 0 Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer) If dPrice <> 0 Then If dPrice < Row.dPrice Then Row.TickSign = 1 If dPrice = Row.dPrice Then Row.TickSign = 0 If dPrice > Row.dPrice Then Row.TickSign = -1 End If If dPrice = 0 Then Row.TickSign_IsNull = True dPrice = Row.dPrice End Sub End Class === Edited by Chris Honcoop @ 25 Jan 2007 2:32 AM UTC===Edit: Note if you want to use this.. it assumes price is never 0 :) If you want to use this and there are Free things where you work you'd have to use some other "impossible number" :)

This is a very good candidate for a custom component. I wonder if there's potential for a whole new raft of components that do things similar to this. More accurately, components that can compare rows in the same pipeline and do "somrthing" appropriately. Currently, there is no semblance of interaction between rows in the pipeline - as there probably shouldn't be in a set-based tool such as this is. Any ideas, cos I don't have any? Something suitably generic but still useful is what I'm after. I'm thinking out loud here and I probably shouldn't be :) -Jamie

I certainly think it is possible to have some kind of component that is basically a component like "Derived Column" except it allows has items like "prior x" (x being the column name) ...and then you could do all the calcs that the normal derived column component allow. Like I mentioned in what I am working on now (got the datawarehouse etl all done so now I am doing dw to marts) I get to do all kinds of fun stuff like moving averages on the dataflow which look back much more than one row. I worked on one today that required each row have "knowledge" of up to 5 rows prior and 5 rows trailing. (needless to say I did that in SQL with a temp table and an ID column and used self joins.. I didn't even want to try that in SSIS). At least in my line of work this type of stuff is needed.. but I guess you could say its not really in the realm of "normal ETL".. its more statistical analysis etc Maybe with the way some software has gone lately Microsoft (or some third party) could release a "statistics package" add-on for SSIS for additional money. Some of my coworkers are building such entire sets of custom stats functions in the CLR that SQL could never handle (or at least never handle gracefully) prior to 2005+CLR.

Chris Honcoop wrote: I certainly think it is possible to have some kind of component that is basically a component like"Derived Column" except it allows has items like "prior x" (x being the column name) ...and then you could do all the calcs that the normal derived column component allow. Like I mentioned in what I am working on now (got the datawarehouse etl all done so now I am doing dw to marts) I get to do all kinds of fun stuff like moving averages on the dataflow which look back much more than one row. I worked on one today that required each row have "knowledge" of up to 5 rows prior and 5 rows trailing. (needless to say I did that in SQL with a temp table and an ID column and used self joins.. I didn't even want to try that in SSIS). Well the functionality wouldn't actually be any different. Its possible to mimic temp tables and self-joins in the pipeline. The only limitation (and its one that really annoys me) is that MERGE JOIN doesn't allow inequality joins. Chris Honcoop wrote: At least in my line of work this type of stuff is needed.. but I guess you could say its not really in the realm of "normal ETL".. its more statistical analysis etc Maybe with the way some software has gone lately Microsoft (or some third party) could release a "statistics package" add-on for SSIS for additional money. Some of my coworkers are building such entire sets of custom statsfunctions in the CLR that SQL could never handle (or at least never handle gracefully) prior to 2005+CLR. Suggest it at http://connect.microsoft.com/sqlserver/feedback. I bet you've got lots of scenarios that they've never even considered and specialised statistical analysis type stuff is something that they could look to provide in Enterprise Edition. The scope here is pretty large. If you do submit somethnig, put alink back up here so that I can comment and vote - and also link back to this thread as well so that they know where the embryonic idea started. -Jamie

I made the suggestion here: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=253840

Cool. I voted and left a comment.

I *almost* voted and left a comment, but realized I couldn't contribute anything more than what Jamie hasn't already said. Nothing pertains to me at the moment.I also should be honest about this and would guess that Microsoft is a long way away from designing something like this. I feel it's probably better left to third parties to develop a custom transformation of this sort.... But, it can't hurt to ask!

Here is an example of comparing rows with a Script Component: http://microsoft-ssis.blogspot.com/2011/04/compare-values-of-two-rows.html This example calculates the sales per week if you only have cumulative sales: Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com